package com.tsj.model.controller;

import com.tsj.model.model.vo.*;
import com.tsj.model.utils.ClassUtil;
import com.tsj.model.utils.ExcelTools;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.Ole10Native;
import org.apache.poi.poifs.filesystem.Ole10NativeException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.*;
import java.math.BigDecimal;
import java.net.URL;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * @author taoshujian
 * @version 1.0
 * @since 2025-09-24 18:47:05
 */
@RestController
@Tag(name = "poi工具使用", description = "poi工具使用列表")
@RequestMapping("/excel")
public class ExcelController {

    @PostMapping("/hero")
    public void hero() throws FileNotFoundException {
        try {
            String url = "D://1.xlsx";
            String outputDir = "D://";
            if (!url.endsWith(".xlsx")) {
                throw new IllegalArgumentException("不支持的文件格式，仅支持 .xlsx");
            }

            FileInputStream inputStream = new FileInputStream(new File(url));
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream); // 处理Excel 2007+
            XSSFSheet sheet = workbook.getSheetAt(0);

            //获取附件信息
            XSSFDrawing drawing = sheet.getDrawingPatriarch();
            if (drawing == null) {
                System.out.println("文件内没有附件！");
                return;
            }
            Map<String, HeroInfoVo> fileVos = new HashMap<>();
            // 遍历所有形状，寻找图片形状并关联图片数据
            for (XSSFShape shape : drawing.getShapes()) {
                // 判断是否为图片形状（通过是否包含图片索引）
                if (shape instanceof XSSFPicture) {
                    XSSFPicture picture = (XSSFPicture) shape;

                    XSSFPictureData picData = picture.getPictureData(); // 直接获取图片数据（无需索引）
                    if (picData == null) {
                        continue; // 过滤无效图片
                    }

                    // 获取图片格式
                    String mimeType = picData.getMimeType();
                    String format = ExcelTools.getFormatFromMimeType(mimeType);

                    // 获取图片所在单元格
                    XSSFClientAnchor anchor = picture.getClientAnchor();
                    String cellAddress = ExcelTools.getCellAddress(anchor.getRow1(), anchor.getCol1());

                    System.out.println("读取到图片：mimeType=" + mimeType + " format=" + format + "  name= xy=" + cellAddress);
                    HeroInfoVo heroInfoVo = new HeroInfoVo();
                    heroInfoVo.setFileCellddress(cellAddress);
                    heroInfoVo.setFileName(cellAddress + "." + format);
                    InputStream is = new ByteArrayInputStream(picData.getData());
                    heroInfoVo.setIs(is);
                    fileVos.put(cellAddress, heroInfoVo);
                } else if (shape instanceof XSSFObjectData) {
                    XSSFObjectData objectData = (XSSFObjectData) shape;
                    String fileName = objectData.getFileName();

                    XSSFAnchor anchor = shape.getAnchor();
                    XSSFClientAnchor clientAnchor = (XSSFClientAnchor) anchor;
                    String cellAddress = ExcelTools.getCellAddressByAnchor(clientAnchor);
                    byte[] content = objectData.getObjectData();

                    InputStream is = new ByteArrayInputStream(content);
                    POIFSFileSystem fs = new POIFSFileSystem(is);
                    Ole10Native nativeObject = Ole10Native.createFromEmbeddedOleObject(fs);

                    System.out.println("读取到PDF：fileName=" + fileName + " ContentType=" + objectData.getContentType() + "  name=" + nativeObject.getLabel2() + " xy=" + cellAddress);
                    HeroInfoVo heroInfoVo = new HeroInfoVo();
                    heroInfoVo.setFileCellddress(cellAddress);
                    heroInfoVo.setFileName(nativeObject.getLabel2());
                    heroInfoVo.setIs(new ByteArrayInputStream(nativeObject.getDataBuffer()));
                    fileVos.put(cellAddress, heroInfoVo);
                }
            }

            Map<Integer, HeroInfoVo> heroInfoVos = new HashMap<>();
            for (Row row : sheet) {
                if (row.getRowNum() <= 1) {
                    continue;
                }
                if (row == null) {
                    continue;
                }
                HeroInfoVo heroInfoVo = new HeroInfoVo();
                int index = row.getRowNum() + 1;
                Cell cellA = row.getCell(0);
                if (!ExcelTools.verifyString(cellA)) {
                    System.out.println("第" + index + "行，修真星列值错误");
                    break;
                }
                heroInfoVo.setPlanetName(ExcelTools.getCellValueAsString(cellA));

                Cell cellB = row.getCell(1);
                if (!ExcelTools.verifyString(cellB)) {
                    System.out.println("第" + index + "行，修真国列值错误");
                    break;
                }
                heroInfoVo.setNationName(ExcelTools.getCellValueAsString(cellB));

                Cell cellC = row.getCell(2);
                if (!ExcelTools.verifyString(cellC)) {
                    System.out.println("第" + index + "行，修真门派列值错误");
                    break;
                }
                heroInfoVo.setClanName(ExcelTools.getCellValueAsString(cellC));

                Cell cellD = row.getCell(3);
                if (!ExcelTools.verifyString(cellD)) {
                    System.out.println("第" + index + "行，人物名称列值错误");
                    break;
                }
                heroInfoVo.setHeroName(ExcelTools.getCellValueAsString(cellD));

                Cell cellE = row.getCell(4);
                if (!ExcelTools.verifyDate(cellE)) {
                    System.out.println("第" + index + "行，日期列值错误");
                    break;
                }
                heroInfoVo.setDate(cellE.getDateCellValue());

                Cell cellF = row.getCell(5);
                if (!ExcelTools.verifyNumeric(cellF)) {
                    System.out.println("第" + index + "行，极品仙石列值错误");
                    break;
                }
                heroInfoVo.setAmount(BigDecimal.valueOf(cellF.getNumericCellValue()));

                Cell cellG = row.getCell(6);
                if (!ExcelTools.verifyNumeric(cellG)) {
                    System.out.println("第" + index + "行，仙石列值错误");
                    break;
                }
                heroInfoVo.setAmountTax(BigDecimal.valueOf(cellG.getNumericCellValue()));

                Cell cellH = row.getCell(7);
                if (!ExcelTools.verifyNumeric(cellH)) {
                    System.out.println("第" + index + "行，仙石合计列值错误");
                    break;
                }
                BigDecimal sum = heroInfoVo.getAmountTax().add(heroInfoVo.getAmount());
                heroInfoVo.setAmountNotTax(BigDecimal.valueOf(cellH.getNumericCellValue()));
                if(heroInfoVo.getAmountNotTax().compareTo( sum) != 0){
                    System.out.println("第" + index + "行，仙石合计列值计算错误");
                    break;
                }


                Cell cellI = row.getCell(8);
                if (!ExcelTools.verifyString(cellI)) {
                    System.out.println("第" + index + "行，货币类型列值错误");
                    break;
                }
                String currency = ExcelTools.getCellValueAsString(cellI);
                if ("仙石".equals(currency)) {
                    heroInfoVo.setCurrency(1);
                }else{
                    System.out.println("第" + index + "行，货币类型列值只能为“仙石");
                    break;
                }

                Cell cellJ = row.getCell(9);
                if (!ExcelTools.verifyNumeric(cellJ)) {
                    System.out.println("第" + index + "行，货币汇率列值错误");
                    break;
                }
                heroInfoVo.setExchangeRate(BigDecimal.valueOf(cellJ.getNumericCellValue()));

                Cell cellK = row.getCell(10);
                if (!ExcelTools.verifyString(cellK)) {
                    System.out.println("第" + index + "行，意境列值错误");
                    break;
                }
                heroInfoVo.setAffectName(ExcelTools.getCellValueAsString(cellK));

                Cell cellL = row.getCell(11);
                if (!ExcelTools.verifyString(cellL)) {
                    System.out.println("第" + index + "行，法器列值错误");
                    break;
                }
                heroInfoVo.setWeaponName(ExcelTools.getCellValueAsString(cellL));

                Cell cellM = row.getCell(12);
                if (!ExcelTools.verifyString(cellM)) {
                    System.out.println("第" + index + "行，人物类型列值错误");
                    break;
                }
                String heroType = ExcelTools.getCellValueAsString(cellM);
                if ("修仙者".equals(heroType)) {
                    heroInfoVo.setHeroType(1);
                } else if ("平民".equals(heroType)) {
                    heroInfoVo.setHeroType(2);
                } else {
                    System.out.println("第" + index + "行，人物类型列值错位。只能为修仙者和平民");
                    break;
                }
                if(!fileVos.containsKey("N"+index)){
                    System.out.println("第" + index + "行，图像列没有附件");
                    break;
                }
                HeroInfoVo fileVo = fileVos.get("N"+index);
                heroInfoVo.setIs(fileVo.getIs());
                heroInfoVo.setFileCellddress(fileVo.getFileCellddress());
                heroInfoVo.setFileName(fileVo.getFileName());

                heroInfoVos.put(index, heroInfoVo);
            }
            for (Integer index : heroInfoVos.keySet()){
                HeroInfoVo heroInfoVo = heroInfoVos.get(index);
                ClassUtil.printAllFields(heroInfoVo);
                System.out.println("保存图片："+heroInfoVo.getFileName());
                ExcelTools.saveToFile(heroInfoVo.getIs().readAllBytes(), outputDir, heroInfoVo.getFileName());
            }



        } catch(Exception e){
            e.printStackTrace();
        }
    }

    @Operation(summary = "从URL读取Excel内容")
    @PostMapping("/readExcel")
    public void readExcel(String excelPath) throws Exception {
        readExcelFromUrl(excelPath);

    }

    @Operation(summary = "从URL读取Excel附件")
    @PostMapping("/readExcelFile")
    public void readExcelFile(String excelPath) throws Exception {
        URL url = new URL(excelPath);
        try (
            InputStream is = url.openStream();
             Workbook workbook = new XSSFWorkbook(is)) {

            XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(0); // 处理第一个工作表
            List<AttachmentInfo> allAttachments = ExcelAttachmentExtractor.extractAllAttachments(sheet);

            // 打印所有附件信息
            System.out.println("Excel中所有附件及位置：");
            for (AttachmentInfo attachment : allAttachments) {
                System.out.println(attachment);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        try (InputStream is =  url.openStream();
             Workbook workbook = new XSSFWorkbook(is)) {

            XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(0);
            List<AttachmentInfo> attachments = ExcelAttachmentExtractor2.extractAllAttachments(sheet);

            System.out.println("提取到的附件：");
            for (AttachmentInfo att : attachments) {
                System.out.println(att);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Operation(summary = "从本地读取Excel附件")
    @PostMapping("/readExcelFileByPath")
    public void readExcelFileByPath(String excelPath) throws Exception {
        FileInputStream file = new FileInputStream(new File(excelPath));
        try (
                InputStream is = file;
                Workbook workbook = new XSSFWorkbook(is)) {

            XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(0); // 处理第一个工作表
            List<AttachmentInfo> allAttachments = ExcelAttachmentExtractor.extractAllAttachments(sheet);

            // 打印所有附件信息
            System.out.println("Excel中所有附件及位置：");
            for (AttachmentInfo attachment : allAttachments) {
                System.out.println(attachment);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 从URL读取Excel文件内容
     * @param excelUrl Excel文件的网络URL
     */
    public static void readExcelFromUrl(String excelUrl) throws Exception {
        // 1. 从URL获取输入流
        URL url = new URL(excelUrl);
        try (InputStream inputStream = url.openStream()) {

            // 2. 根据文件后缀判断Excel版本（.xls 或 .xlsx）
            Workbook workbook;
            if (excelUrl.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream); // 处理Excel 2007+
            } else if (excelUrl.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream); // 处理Excel 97-2003
            } else {
                throw new IllegalArgumentException("不支持的文件格式，仅支持 .xls 和 .xlsx");
            }
            List<Map<String, Object>> result = new ArrayList<>();
            // 3. 遍历工作表
            for (int sheetNum = 0; sheetNum < 1; sheetNum++) {
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if (sheet == null) continue;

                System.out.println("===== 工作表名称：" + sheet.getSheetName() + " =====");

                Row headerRow = sheet.getRow(0);
                List<String> headers = new ArrayList<>();
                if(headerRow == null) continue;
                for (Cell cell : headerRow) {
                    headers.add(ExcelTools.getCellValue(cell).toString());
                }

                // 4. 遍历行（从第0行开始）
                for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if (row == null) continue;

                    // 5. 遍历单元格
                    Map<String, Object> rowData2 = new HashMap<>();
                    StringBuilder rowData = new StringBuilder();
                    for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                        Cell cell = row.getCell(cellNum);
                        String cellValue = ExcelTools.getCellValue(cell); // 获取单元格值
                        rowData.append(cellValue).append("\t"); // 用制表符分隔

                        rowData2.put(headers.get(cellNum), null);
                    }
                    System.out.println("行 " + rowNum + "：" + rowData);
                    // 提取单元格中的附件（图片/PDF等，仅支持嵌入的OLE对象）
                    extractAttachments(sheet, rowData2);
                    result.add(rowData2);
                }
            }
            // 3. 打印结果
            for (Map<String, Object> row : result) {
                System.out.println("行数据: " + row);
            }

            // 关闭工作簿
            workbook.close();
        }
    }

    /**
     * 提取单元格中的附件（图片、PDF等，适配POI 5.x版本）
     */
    private static void extractAttachments(Sheet sheet, Map<String, Object> rowData) throws IOException {
        // 仅处理.xlsx格式（XSSF）
        if (sheet instanceof XSSFSheet) {
            XSSFSheet xssfSheet = (XSSFSheet) sheet;

            // POI 5.x中通过getDrawingPatriarch()获取绘图对象（替代旧版的getDrawings()）
            XSSFDrawing drawing = xssfSheet.getDrawingPatriarch();
            if (drawing == null) {
                return; // 工作表中无绘图对象（无图片/附件）
            }

            // 遍历所有绘图形状
            for (XSSFShape shape : drawing.getShapes()) {
                // 处理PDF等OLE嵌入对象
                if (shape instanceof XSSFObjectData) {
                    XSSFObjectData objectData = (XSSFObjectData) shape;
                    String fileName = objectData.getFileName();
                    byte[] content = objectData.getObjectData();
                    if (fileName != null && content != null && content.length > 0) {
                        rowData.put("附件_" + fileName, content);
                        System.out.println("附件名称1：" + fileName);
                    }
                }
                // 处理JPG/PNG等图片
                else if (shape instanceof XSSFPicture) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    String fileExt = picture.getPictureData().suggestFileExtension();
                    String fileName = "image_" + System.currentTimeMillis() + "." + (fileExt != null ? fileExt : "jpg");
                    byte[] imageData = picture.getPictureData().getData();
                    if (imageData != null && imageData.length > 0) {
                        rowData.put("图片_" + fileName, imageData);
                        System.out.println("图片名称1：" + fileName);
                    }
                }
            }
        }
    }

}
